JCapper Message Board

JCapper 101
-- Excel Import

Home	Register Log In

Excel Import

Tony_N
10/1/2018
1:07:04 AM

Hi everyone, I believe this question may have been answered but I am new, so here goes.

I take the *.JCP files and *.Txt files into excel with an automated routine. I'm exploring an old angle of mine and cannot get it through the UDM/Data Window.

Trouble is in the files "6-4" in the exacta field will become 4-July...,

Now I am processing hundreds of files so manual correction is not possible.

Help will be appreciated

Best

tony

jeff
10/1/2018
10:14:43 AM

Spent about 30 minutes reading posts on Microsoft support forums like this one --

Stop auto correction of number into a date:
https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_other-mso_2007/stop-auto-correction-of-number-into-a-date/9968c54a-221b-4b18-a3d1-cfd3d312a8a6

--quote:

"When i work with excel, i do data entry, some of the numbers that i type in can be interpreted as dates. when this happens excel automatically changes the format of the cell from general, to date. i want to be able to stop this auto correction.

I know there are a number of ways to avoid this happening, but i am fed up with these methods. i simply want to turn the auto correct off, so that instead of what i type into the cell changing, what i type into the cell shows exactly what i typed in.

really hope there is someone out the who knows how to do this.

thanks"

--end quote

Tony, it looks like, for years, lots of Excel users out there have not only been experiencing the same problem you are reporting --

The unwanted behavior in Excel where numbers that aren't dates are auto-converted to dates --

And while aware they can format individual columns to display data as text, which does provide a solution at the individual spreadsheet level --

That 'solution' doesn't work for them because it's just too labor intensive.

For years they've been asking Microsoft for a solution at the Excel program level --

And without success.

Tell U what --

I see this as something that can probably be addressed from within JCapper before the data gets written to the hard drive.

I'm guessing I can come up with a character combination...

Maybe the ability through a JCapper setting to wrap winning exacta-tri-super-dd-p3-p4-p5-p6 numbers in the data inside of single quote characters...

For example 6-4 becomes '6-4'

Or something similar that leaves the winning numbers readable to the human eye...

But at the same time stops Excel from auto converting those to a date.

I'll need to do some research first --

And I'll come back to this thread and post a follow up once I have some ideas in the way of a working solution.

-jp

.

~Edited by: jeff on: 10/1/2018 at: 10:14:43 AM~

Tony_N
10/1/2018
12:35:55 PM

Thankyou Jeff

For now I don't use this data so its no rush, also if I find a solution ill post it here, I might be able to just target the few fields in the text import formatting dialog box that this appears to occur with, my first solution was to try to define the format of all the fields >1000 :) but "too many continuations" error popped up.

Best

Tony

Tony_N
10/1/2018
3:32:36 PM

Problem Solved.

The problem of formatting as dates does not appear in *.JCP files, only the *F.Txt results files and is confined to the program numbers fields in the exotic results section. Theses are fields 135,141,...,243 (every 6th field).
The dashes "-" can cause excel to interpret the data as a date.

First what did not work, I edited the field info array specifically for those fields changing from general "1" to "2" but excel ignored it.

Then I listed the entire 1 to 244 array, allowed the lines to be very long to avoid the "Too Many Continuations" error and left all formats as 1 general, except the 135,141,...,243 fields which I change to 2. Ran it and now all are text no dates.

Not elegant but this works

Best Tony

Tony_N
10/1/2018
3:51:04 PM

I'll share the code just in case someone else has this issue, I tested it as a macro before incorporating it, here it is,

Best

Tony

Sub Macro2()
'
' Macro2 Macro
'

'
Workbooks.OpenText Filename:="D:\Jcapperdata\2018\Q3_2018\ALB0812F.TXT", _
Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), _
Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1), Array(33, 1), Array(34, 1), _
Array(35, 1), Array(36, 1), Array(37, 1), Array(38, 1), Array(39, 1), Array(40, 1), Array(41, 1), Array(42, 1), Array(43, 1), Array(44, 1), Array(45, 1), Array(46, 1), Array(47, 1), _
Array(48, 1), Array(49, 1), Array(50, 1), Array(51, 1), Array(52, 1), Array(53, 1), Array(54, 1), Array(55, 1), Array(56, 1), Array(57, 1), Array(58, 1), Array(59, 1), Array(60, 1), _
Array(61, 1), Array(62, 1), Array(63, 1), Array(64, 1), Array(65, 1), Array(66, 1), Array(67, 1), Array(68, 1), Array(69, 1), Array(70, 1), Array(71, 1), Array(72, 1), Array(73, 1), _
Array(74, 1), Array(75, 1), Array(76, 1), Array(77, 1), Array(78, 1), Array(79, 1), Array(80, 1), Array(81, 1), Array(82, 1), Array(83, 1), Array(84, 1), Array(85, 1), Array(86, 1), _
Array(87, 1), Array(88, 1), Array(89, 1), Array(90, 1), Array(91, 1), Array(92, 1), Array(93, 1), Array(94, 1), Array(95, 1), Array(96, 1), Array(97, 1), Array(98, 1), Array(99, 1), _
Array(100, 1), Array(101, 1), Array(102, 1), Array(103, 1), Array(104, 1), Array(105, 1), Array(106, 1), Array(107, 1), Array(108, 1), Array(109, 1), Array(110, 1), Array(111, 1), _
Array(112, 1), Array(113, 1), Array(114, 1), Array(115, 1), Array(116, 1), Array(117, 1), Array(118, 1), Array(119, 1), Array(120, 1), Array(121, 1), Array(122, 1), Array(123, 1), _
Array(124, 1), Array(125, 1), Array(126, 1), Array(127, 1), Array(128, 1), Array(129, 1), Array(130, 1), Array(131, 1), Array(132, 1), Array(133, 1), Array(134, 1), Array(135, 2), _
Array(136, 1), Array(137, 1), Array(138, 1), Array(139, 1), Array(140, 1), Array(141, 2), Array(142, 1), Array(143, 1), Array(144, 1), Array(145, 1), Array(146, 1), Array(147, 2), _
Array(148, 1), Array(149, 1), Array(150, 1), Array(151, 1), Array(152, 1), Array(153, 2), Array(154, 1), Array(155, 1), Array(156, 1), Array(157, 1), Array(158, 1), Array(159, 2), _
Array(160, 1), Array(161, 1), Array(162, 1), Array(163, 1), Array(164, 1), Array(165, 2), Array(166, 1), Array(167, 1), Array(168, 1), Array(169, 1), Array(170, 1), Array(171, 2), _
Array(172, 1), Array(173, 1), Array(174, 1), Array(175, 1), Array(176, 1), Array(177, 2), Array(178, 1), Array(179, 1), Array(180, 1), Array(181, 1), Array(182, 1), Array(183, 2), _
Array(184, 1), Array(185, 1), Array(186, 1), Array(187, 1), Array(188, 1), Array(189, 2), Array(190, 1), Array(191, 1), Array(192, 1), Array(193, 1), Array(194, 1), Array(195, 2), _
Array(196, 1), Array(197, 1), Array(198, 1), Array(199, 1), Array(200, 1), Array(201, 2), Array(202, 1), Array(203, 1), Array(204, 1), Array(205, 1), Array(206, 1), Array(207, 2), _
Array(208, 1), Array(209, 1), Array(210, 1), Array(211, 1), Array(212, 1), Array(213, 2), Array(214, 1), Array(215, 1), Array(216, 1), Array(217, 1), Array(218, 1), Array(219, 2), _
Array(220, 1), Array(221, 1), Array(222, 1), Array(223, 1), Array(224, 1), Array(225, 2), Array(226, 1), Array(227, 1), Array(228, 1), Array(229, 1), Array(230, 1), Array(231, 2), _
Array(232, 1), Array(233, 1), Array(234, 1), Array(235, 1), Array(236, 1), Array(237, 2), Array(238, 1), Array(239, 1), Array(240, 1), Array(241, 1), Array(242, 1), Array(243, 2), _
Array(244, 1))
End Sub

jeff
10/2/2018
5:11:18 AM

Nice!

-jp

.

JCapper Message Board

JCapper 101 -- Excel Import

JCapper 101
-- Excel Import